<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>格式化sql</title>
    <link type="text/css" rel="stylesheet" href="formatSql.css">
    <script type="text/javascript" src="../lib/jsdk.lang.es5.js"></script>
    <script type="text/javascript" src="../lib/jsdk.utils.es5.js"></script>
    <script type="text/javascript" src="../lib/jquery-3.3.1.min.js"></script>
</head>
<body>
<h5>1. insert语句转换为mybatis占位符insert语句(从数据库客户端工具中复制出insert语句粘贴到下方输入框)</h5>
<textarea id="insertOriginSql" style="width: 100%;height: 300px"></textarea>
<button onclick="transfer2Insert()">INSERT</button>
<button onclick="transfer2BatchInsert()">BATCH INSERT</button>
<button onclick="transfer2Select()">SELECT</button>

<h5>2. 石墨文档表格转建表语句(在石墨文档中选中表格中所有的数据部分，【复制=>粘贴】到下方输入框)</h5>
<textarea id="shimoToTableSql" style="width: 100%;height: 300px"></textarea>
<button onclick="transferShimoToTable()">生成建表语句</button>

<h5>3. 石墨文档表格转实体类(在石墨文档中选中表格中所有的数据部分，【复制=>粘贴】到下方输入框)</h5>
<textarea id="shimoToBean" style="width: 100%;height: 300px"></textarea>
<button onclick="transferShimoToBean()">生成实体类</button>

<h5>4. 石墨文档表格转mybatis ResultMap(在石墨文档中选中表格中所有的数据部分，【复制=>粘贴】到下方输入框)</h5>
<textarea id="shimoToMybatisResult" style="width: 100%;height: 300px"></textarea>
<button onclick="transferShimoToMybatisResultMap()">生成mybatis ResultMap</button>
</body>
</html>

<script type="text/javascript">

    var sqlGenerator = (function () {
        var InsertSqlGenerator = function () {
            this.insertOperator = " INSERT INTO ";
            this.valueOperator = " VALUES ";
            this.leftBracket = " ( ";
            this.rightBracket = " ) ";
            this.selectOperator = " SELECT ";
            this.fromOperator = " FROM ";
        };

        InsertSqlGenerator.prototype = {
            parse: function (originSql) {
                originSql = this._removeBackQuote(originSql);
                var tableName = this._getTableName(originSql);
                var fieldsStr = this._getFieldsStr(originSql);
                var mybatisFieldsStr = this._getMybatisFieldsStr(originSql);
                return this.insertOperator + tableName + this.leftBracket + fieldsStr + this.rightBracket
                    + this.valueOperator + this.leftBracket + mybatisFieldsStr + this.rightBracket;
            },
            parseBatch: function (originSql) {
                originSql = this._removeBackQuote(originSql);
                var tableName = this._getTableName(originSql);
                var fieldsStr = this._getFieldsStr(originSql);
                var mybatisFieldsStr = this._getMybatisBatchFieldsStr(originSql);
                return this.insertOperator + tableName + this.leftBracket + fieldsStr + this.rightBracket
                    + this.valueOperator + mybatisFieldsStr;

            },
            parseSelect: function (originSql){
                originSql = this._removeBackQuote(originSql);
                var tableName = this._getTableName(originSql);
                var fieldsStr = this._getFieldsStr(originSql);
                return this.selectOperator + fieldsStr + this.fromOperator + tableName;
            },
            _removeBackQuote: function (originSql) {
                return IString.replaceAll(originSql, '`', '');
            },
            _getTableName: function (originSql) {
                var upperCaseSql = originSql.toString().toUpperCase();
                var tableName = upperCaseSql.match(/\s*INSERT\s+INTO\s+(.+?)\(/)[1];
                if (IString.contains(tableName, '.')) {
                    return IString.substring(tableName, tableName.lastIndexOf('.') + 1);
                }
                return tableName.toLowerCase();
            },
            _getFieldsStr: function (originSql) {
                var fieldStr = IString.substring(originSql, IString.indexOf(originSql, "(") + 1, IString.indexOf(originSql, ")"));
                return IString.trimAll(fieldStr);
            },
            _getFieldArray: function (originSql) {
                var fieldStr = this._getFieldsStr(originSql);
                return IString.split(fieldStr, ',');
            },
            _getMybatisFieldsStr: function (originSql) {
                var fieldArr = this._getFieldArray(originSql);
                var result = '';
                IArray.each(fieldArr, function (index, item) {
                    var _index = item.indexOf("_");
                    var fieldName = item;
                    if (_index > 0) {
                        var matchResult = item.match(/_(.)/g);
                        IArray.each(matchResult, function (index, str) {
                            var replace = IString.replaceAll(str, "_", "");
                            fieldName = IString.replace(fieldName, str, replace.toUpperCase());
                        });
                    }
                    result = result +'#{' + fieldName + "},";
                });
                return result.substring(0, result.lastIndexOf(","));
            },
            _getMybatisBatchFieldsStr: function (originSql) {
                var sql = this._getMybatisFieldsStr(originSql);
                return '\n<foreach collection="list" item="item" index="index" separator=","> \n(' + sql + " ) \n</foreach>";
            }
        };

        var insertSqlGenerator = new InsertSqlGenerator();

        return {
            insertSqlGenerator: insertSqlGenerator
        }

    })();

    //INSERT INTO `oms`.`oms_redpackage_send_rule` (`id`, `redPackageId`) VALUES (NULL, NULL);
    //INSERT INTO oms.oms_redpackage_send_rule  ( id, redPackageId, scene, rewardType, rewardRule, cycleType, cycleDay, pushText, pushSMS ) VALUES ( #{id}, #{ redPackageId}, #{ scene}, #{ rewardType}, #{ rewardRule}, #{ cycleType}, #{ cycleDay}, #{ pushText}, #{ pushSMS},  )
    function transferUpdateOriginSql() {

    }

    function transfer2Insert() {
        var originSql = $('#insertOriginSql').val();
        if (!originSql) {
            alert('sql为空！');
            return;
        }
        var result = sqlGenerator.insertSqlGenerator.parse(originSql);
        $('#insertOriginSql').val(result);
    }

    function transfer2BatchInsert() {
        var originSql = $('#insertOriginSql').val();
        if (!originSql) {
            alert('sql为空！');
            return;
        }
        var result = sqlGenerator.insertSqlGenerator.parseBatch(originSql);
        $('#insertOriginSql').val(result);

    }
    
    function transfer2Select() {
        var originSql = $('#insertOriginSql').val();
        if (!originSql) {
            alert('sql为空！');
            return;
        }

        var result = sqlGenerator.insertSqlGenerator.parseSelect(originSql);
        $('#insertOriginSql').val(result);

    }

    function transferShimoToTable() {
        var originSql = $('#shimoToTableSql').val();
        if (!originSql) {
            alert('内容不能为空！');
            return;
        }
        var fieldArr = originSql.trim().split(/\n/);
        var sqlParamsArr = createTwoDimensionalArray(fieldArr.length, 3);
        for (var i = 0; i < fieldArr.length; i++) {
            var oneLine = fieldArr[i];
            //todo 去掉字符串中以(开头以）结尾的部分中间的空格
            var filedNameArr = oneLine.split(/\s+/);
            sqlParamsArr[i][0] = filedNameArr[0];
            sqlParamsArr[i][1] = filedNameArr[1];
            if (filedNameArr.length > 2) {
                sqlParamsArr[i][2] = filedNameArr.slice(2).join("");
            }
        }
        var sql = "CREATE TABLE TableName (\n";
        for (var j = 0; j < sqlParamsArr.length; j++) {
            var sqlParamArr = sqlParamsArr[j];
            sql += createFieldDescription(sqlParamArr);
        }
        sql += "  PRIMARY KEY (id)\n)";
        $('#shimoToTableSql').val(sql);
    }

    function createFieldDescription(sqlParamArr) {
        var sql = '';
        var fieldName = sqlParamArr[0];
        var fieldType = sqlParamArr[1];
        var fieldComment = sqlParamArr[2];
        if ('createTime' == fieldName) {
            sql = "  " + fieldName + " " + fieldType + " NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '" + fieldComment + "',\n";
        } else if ('updateTime' == fieldName) {
            sql = "  " + fieldName + " " + fieldType + " NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '" + fieldComment + "',\n";
        } else if ('id' == fieldName) {
            sql = "  " + fieldName + " " + fieldType + " NOT NULL AUTO_INCREMENT COMMENT '" + fieldComment + "',\n";
        } else {
            sql = "  " + fieldName + " " + fieldType + " COMMENT '" + fieldComment + "',\n";
        }
        return sql;
    }

    function transferShimoToBean() {
        var originSql = $('#shimoToBean').val();
        if (!originSql) {
            alert('内容不能为空！');
            return;
        }

        var fieldArr = originSql.trim().split(/\n/);
        var sqlParamsArr = createTwoDimensionalArray(fieldArr.length, 3);
        for (var i = 0; i < fieldArr.length; i++) {
            var filedNameArr = fieldArr[i].split(/\s+/);
            sqlParamsArr[i][0] = filedNameArr[0];
            sqlParamsArr[i][1] = filedNameArr[1];
            if (filedNameArr.length > 2) {
                sqlParamsArr[i][2] = filedNameArr.slice(2).join("");
            }
        }

        var result = "";
        for (var j = 0; j < sqlParamsArr.length; j++) {
            var sqlParamArr = sqlParamsArr[j];
            result += "/** " + sqlParamArr[2] + " */\nprivate " + getJavaClassType(sqlParamArr[1]) + " " + sqlParamArr[0] + ";\n";
        }

        $('#shimoToBean').val(result);

    }

    function transferShimoToMybatisResultMap() {
        var originSql = $('#shimoToMybatisResult').val();
        if (!originSql) {
            alert('内容不能为空！');
            return;
        }
        var fieldArr = originSql.trim().split(/\n/);
        var sqlParamsArr = createTwoDimensionalArray(fieldArr.length, 3);
        for (var i = 0; i < fieldArr.length; i++) {
            var oneLine = fieldArr[i];
            //todo 去掉字符串中以(开头以）结尾的部分中间的空格
            var filedNameArr = oneLine.split(/\s+/);
            sqlParamsArr[i][0] = filedNameArr[0];
            sqlParamsArr[i][1] = filedNameArr[1];
            if (filedNameArr.length > 2) {
                sqlParamsArr[i][2] = filedNameArr.slice(2).join("");
            }
        }

        var result = '<resultMap id="xxxx" type="com.zhoupu.xxx">\n';
        for (var j = 0; j < sqlParamsArr.length; j++) {
            var fieldName = sqlParamsArr[j][0];
            if ('id' == fieldName) {
                result += '  <id property="id" column="id" />\n';
            } else {
                result += '  <result property="' + fieldName + '" column="' + fieldName + '" />\n';
            }
        }
        result += '</resultMap>';
        $('#shimoToMybatisResult').val(result);
    }

    function getJavaClassType(type) {
        var lowCaseType = type.toLowerCase();
        if (lowCaseType.indexOf("bigint") >= 0) {
            return "Long";
        } else if (lowCaseType.indexOf("int") >= 0) {
            return "Integer";
        } else if (lowCaseType.indexOf("bit") >= 0) {
            return "Boolean";
        } else if (lowCaseType.indexOf("double") >= 0) {
            return "Double";
        } else if (lowCaseType.indexOf("float") >= 0) {
            return "Float";
        } else if (lowCaseType.indexOf("decimal") >= 0) {
            return "BigDecimal";
        } else if (lowCaseType.indexOf("date") >= 0 || lowCaseType.indexOf("time") >= 0 || lowCaseType.indexOf("year") >= 0) {
            return "Date";
        } else {
            return "String";
        }
    }


    function createTwoDimensionalArray(a, b) {
        var arr = new Array(a);
        for (var i = 0; i < arr.length; i++) {
            arr[i] = new Array(b);
        }
        return arr;
    }

</script>